Imports System.Text

Public Class CMainLogic : Inherits CTableInformation

#Region "Enums"
    Public Enum EQueryType
        Normal
        Paged
        Count
        Trans 'actional
    End Enum
#End Region

#Region "Interface (Must Override)"
    Public Overrides Function Generate(ByVal overwrite As COverwriteFiles) As Boolean
        If IsNothing(overwrite) Then Return False
        GenerateClasses(overwrite)
        If Me.Architecture = EArchitecture.StoredProcs Then
            GenerateStoredProcs(overwrite)
        End If
        Return True
    End Function
#End Region

#Region "Private - Main Flow (Classes)"
    Public Sub GenerateClasses(ByVal overwrite As COverwriteFiles)
        If overwrite.AutoGenerated Then GenerateClassAuto()
        If overwrite.AutoGenerated Then GenerateClassAutoList()
        If overwrite.Customizable Then GenerateClassCustom()
        If overwrite.Customizable Then GenerateClassCustomList()
    End Sub
    Public Sub GenerateClassAuto()
        Dim template As New CTemplate(ClassFileName, TemplateFolderClasses(True))
        Dim dr_tbl As IDataReader
        Dim dr_vw As IDataReader

        With template
            .Replace("NameSpace", CSharpNamespace)
            If Me.UseAuditTrail Then
                .Replace("BaseClass", String.Concat("SchemaAudit.CBase", Me.Architecture, "Audited"))
            Else
                .Replace("BaseClass", String.Concat("CBase", Me.Architecture.ToString))
            End If
            .Replace("ClassName", ClassName)
            .Replace("TableName", TableName)
            .Replace("SchemaInfo", SchemaInfo)
            .Replace("ViewNameOverride", ViewNameOverride)
            .Replace("Singular", Singular(TableName))
            .Replace("PrimaryKeyName", PrimaryKeyName)
            .Replace("SecondaryKeyName", SecondaryKeyName)
            .Replace("TertiaryKeyName", TertiaryKeyName)
            .Replace("Caching", Caching)
            .Replace("NameSpace", CSharpNamespace)

            dr_tbl = GetTable()
            dr_tbl.Read()

            dr_vw = GetView()
            dr_vw.Read()
            Try
                .Replace("PrimaryKeyInfo", PrimaryKeyInfo(dr_tbl))
                .Replace("PrimaryKeyObjectType", PrimaryKeyObjectType(dr_tbl))
                .Replace("PrimaryKeyColumns", PrimaryKeyColumns(dr_tbl))
                .Replace("TableColumnProperties", TableColumnProperties(dr_tbl))
                .Replace("ViewColumnProperties", ViewColumnProperties(dr_tbl, dr_vw))
                .Replace("CompareTo", CompareToFunction)
                .Replace("ToXml", ToXml(dr_tbl))
                .Replace("AuditTrail", CStr(IIf(Me.UseAuditTrail, AuditTrail(dr_tbl), String.Empty)))
                .Replace("QueryCasting", QueryCasting(dr_tbl))
                .Replace("InitValues", InitValues(dr_tbl))

                .Replace("Members", ColumnMembers(dr_vw))
                .Replace("CopyConstructor", CopyConstructor(dr_vw))
                .Replace("Persistance", Persistance(dr_vw, dr_tbl))
            Catch
                Throw
            Finally
                dr_tbl.Close()
                dr_vw.Close()
            End Try

            'Generate Class
            Dim filePath As String = String.Concat(TargetFolder, "/tables/", EntityName, "/", FileName(True, False))

            WriteFile(filePath, .Template)
        End With
    End Sub
    Public Sub GenerateClassAutoList()
        Dim template As New CTemplate(ListFileName, TemplateFolderClasses(True))
        Dim dr As IDataReader

        With template
            .Replace("NameSpace", CSharpNamespace)
            .Replace("ClassName", ClassName)
            .Replace("PrimaryKeyName", PrimaryKeyName)
            .Replace("SecondaryKeyName", SecondaryKeyName)
            .Replace("TertiaryKeyName", TertiaryKeyName)

            .Replace("PrimaryKeyNameCamelCase", CamelCase(PrimaryKeyName))
            .Replace("PrimaryKeyNameProperCase", ProperCase(PrimaryKeyName))
            If Me.Is3Way Or Me.IsManyToMany Then
                .Replace("SecondaryKeyNameCamelCase", CamelCase(SecondaryKeyName))
                .Replace("SecondaryKeyNameProperCase", ProperCase(SecondaryKeyName))
                If Me.Is3Way Then
                    .Replace("TertiaryKeyNameCamelCase", CamelCase(TertiaryKeyName))
                    .Replace("TertiaryKeyNameProperCase", ProperCase(TertiaryKeyName))
                End If
            End If

            .Replace("GenericSorting", GenericSorting)

            dr = GetTable()
            dr.Read()
            Try
                .Replace("SaveAllDeleteAll", SaveAllDeleteAll(dr))
                .Replace("PrimaryKeyObjectType", PrimaryKeyObjectType(dr))
                .Replace("SecondaryKeyObjectType", SecondaryKeyObjectType(dr))
                .Replace("TertiaryKeyObjectType", TertiaryKeyObjectType(dr))
                .Replace("CustomIndices", CustomIndices(dr))
                .Replace("MoveUpDown", MoveUpDown(dr))
            Catch
                Throw
            Finally
                dr.Close()
            End Try

            'Generate Class
            Dim filePath As String = String.Concat(TargetFolder, "/tables/", EntityName, "/", FileName(True, True))
            WriteFile(filePath, .Template)
        End With
    End Sub
    Public Sub GenerateClassCustom()
        Dim template As New CTemplate(ClassFileName, TemplateFolderClasses(False))
        With template
            .Replace("NameSpace", CSharpNamespace)
            .Replace("ClassName", ClassName)
            .Replace("Singular", Singular)
            .Replace("Plural", Plural)

            .Replace("PrimaryKeyNameCamelCase", CamelCase(PrimaryKeyName))
            .Replace("PrimaryKeyNameProperCase", ProperCase(PrimaryKeyName))
            If Me.Is3Way Or Me.IsManyToMany Then
                .Replace("SecondaryKeyNameCamelCase", CamelCase(SecondaryKeyName))
                .Replace("SecondaryKeyNameProperCase", ProperCase(SecondaryKeyName))
                If Me.Is3Way Then
                    .Replace("TertiaryKeyNameCamelCase", CamelCase(TertiaryKeyName))
                    .Replace("TertiaryKeyNameProperCase", ProperCase(TertiaryKeyName))
                End If
            End If

            Dim dr As IDataReader = GetTable()
            dr.Read()
            Try
                .Replace("PrimaryKeyObjectType", PrimaryKeyObjectType(dr))
                .Replace("SecondaryKeyObjectType", SecondaryKeyObjectType(dr))
                .Replace("TertiaryKeyObjectType", TertiaryKeyObjectType(dr))
                If Is3Way Or IsManyToMany Then
                    .Replace("Search", String.Empty)
                Else
                    .Replace("Search", Search(dr))
                End If

                If Not IsManyToMany Then
                    Dim pkType As Type = dr.GetFieldType(dr.GetOrdinal(PrimaryKeyName))
                    .Replace("PrimaryKeyConstantsInteger", PrimaryKeyConstantsInteger(pkType))
                    .Replace("PrimaryKeyConstantsString", PrimaryKeyConstantsString(pkType))
                End If

                .Replace("ToXml_Items", ToXml(dr))
                '.Replace("CacheQueries", CacheQueries(dr)) 'Cached data doesnt need db queries, but could include as commented-out code

                dr.Close()
            Catch ex As Exception
                dr.Close()
                Throw ex
            End Try

            Dim filePath As String = String.Concat(TargetFolder, "/tables/", EntityName, "/", FileName(False, False))
            WriteFile(filePath, .Template)
        End With
    End Sub
    Public Sub GenerateClassCustomList()
        With New CTemplate("List.txt", TemplateFolderClasses(False))
            .Replace("NameSpace", CSharpNamespace)
            .Replace("ClassName", ClassName)
            .Replace("Plural", Plural)

            Dim dr As IDataReader = GetView()
            dr.Read()
            Try
                If Is3Way Or IsManyToMany Then
                    .Replace("Search", String.Empty)
                Else
                    .Replace("Search", SearchList(dr))
                End If

                .Replace("CsvHeadings", CsvHeadings(dr))
                .Replace("CsvData", CsvData(dr))
            Catch
                Throw
            Finally
                dr.Close()
            End Try

            Dim filePath As String = String.Concat(TargetFolder, "/tables/", EntityName, "/", FileName(False, True))
            WriteFile(filePath, .Template)
        End With
    End Sub
#End Region

#Region "Private - Main Flow (Stored Procs)"
    Private Sub GenerateStoredProcs(ByVal overwrite As COverwriteFiles)
        'Me.Platform = EPlatform.MySql
        Dim dr As IDataReader = GetTable()
        Try
            Dim filePath As String

            filePath = TargetFolder & "/scripts/" & SpNameDeleteById & ".sql"
            If overwrite.SpDelete Then WriteFile(filePath, SpDeleteById(dr))

            filePath = TargetFolder & "/scripts/" & SpNameInsert & ".sql"
            If overwrite.SpInsert Then WriteFile(filePath, SpInsert)

            filePath = TargetFolder & "/scripts/" & SpNameUpdate & ".sql"
            If overwrite.SpUpdate Then WriteFile(filePath, SpUpdate)

            filePath = TargetFolder & "/scripts/" & SpNameSelectById & ".sql"
            If overwrite.SpSelectSingle Then WriteFile(filePath, SpSelectById(dr))

            filePath = TargetFolder & "/scripts/" & SpNameSelectAll & ".sql"
            If overwrite.SpSelectAll Then WriteFile(filePath, SpSelectAll(dr))

            Dim i As String
            For Each i In Me.OptionalFilters
                filePath = TargetFolder & "/scripts/" & SpNameSelectByColumnName(i) & ".sql"
                WriteFile(filePath, SpSelectByColumnName(i, dr))
            Next


            If ExecuteScripts Then
                If overwrite.SpDelete Then CreateStoredProc(SpNameDeleteById, SpDeleteById(dr))
                If overwrite.SpSelectSingle Then CreateStoredProc(SpNameSelectById, SpSelectById(dr))
                If overwrite.SpInsert Then CreateStoredProc(SpNameInsert, SpInsert)
                If overwrite.SpUpdate Then CreateStoredProc(SpNameUpdate, SpUpdate)
                If overwrite.SpSelectAll Then CreateStoredProc(SpNameSelectAll, SpSelectAll(dr))
                For Each i In Me.OptionalFilters
                    CreateStoredProc(SpNameSelectByColumnName(i), SpSelectByColumnName(i, dr))
                Next
            End If
            dr.Close()
        Catch
            dr.Close()
            Throw
        End Try
    End Sub
    Private Sub CreateStoredProc(ByVal name As String, ByVal code As String)
        Try
            Database.ExecuteNonQuery("DROP PROCEDURE " & name)
        Catch ex As Exception
        End Try

        Dim startAt As Integer = code.IndexOf("CREATE ")
        If -1 <> startAt Then code = code.Substring(startAt)
        Dim stopAt As Integer = code.IndexOf("GO")
        If -1 = stopAt Then stopAt = code.IndexOf("$$")
        If -1 <> stopAt Then code = code.Substring(0, stopAt)
        Try
            Database.ExecuteNonQuery(code)
        Catch ex As Exception
            ThrowError(name, ex)
        End Try
    End Sub
#End Region

#Region "Private - Schema info, Column-Properties, DataTypes"
    Private Function ViewNameOverride() As String
        If Len(ViewName) = 0 Then Return String.Empty
        Dim t As New CTemplate("ViewName.txt", TemplateFolderFragments)
        t.Replace("ViewName", ViewName)
        Return t.Template
    End Function
    Private Function PrimaryKeyObjectType(ByVal dr As IDataReader) As String
        If Len(PrimaryKeyName) = 0 Then PrimaryKeyName = dr.GetName(0)
        Return GetTypeString(dr, PrimaryKeyName)
    End Function
    Private Function SecondaryKeyObjectType(ByVal dr As IDataReader) As String
        If Len(SecondaryKeyName) = 0 Then Return String.Empty
        Return GetTypeString(dr, SecondaryKeyName)
    End Function
    Private Function TertiaryKeyObjectType(ByVal dr As IDataReader) As String
        If Len(TertiaryKeyName) = 0 Then Return String.Empty
        Return GetTypeString(dr, TertiaryKeyName)
    End Function
    Private Function PrimaryKeyObjectTypeVb(ByVal dr As IDataReader) As String
        If Len(PrimaryKeyName) = 0 Then PrimaryKeyName = dr.GetName(0)
        Return DataTypeVb(dr.GetFieldType(dr.GetOrdinal(PrimaryKeyName)))
    End Function
    Private Function SecondaryKeyObjectTypeVb(ByVal dr As IDataReader) As String
        If Len(SecondaryKeyName) = 0 Then Return String.Empty
        Return DataTypeVb(dr.GetFieldType(dr.GetOrdinal(SecondaryKeyName)))
    End Function
    Private Function TertiaryKeyObjectTypeVb(ByVal dr As IDataReader) As String
        If Len(TertiaryKeyName) = 0 Then Return String.Empty
        Return DataTypeVb(dr.GetFieldType(dr.GetOrdinal(TertiaryKeyName)))
    End Function
    Private Function TableColumnProperties(ByVal dr As IDataReader) As String
        Dim fileName As String = CStr(IIf(Me.Architecture <> EArchitecture.Smart, "TableProperty.txt", "TablePropertySmart.txt"))
        Dim template As New CTemplate(fileName, TemplateFolderFragments)
        Dim i As Integer
        Dim s As String = String.Empty
        For i = 0 To dr.FieldCount - 1
            s &= ColumnProperty(dr, i, template, True)
        Next
        Return s
    End Function
    Private Function ViewColumnProperties(ByVal dr_tbl As IDataReader, ByVal dr_vw As IDataReader) As String
        If Len(ViewName) = 0 Then Return String.Empty

        Dim fileName As String = CStr(IIf(Me.Architecture <> EArchitecture.Smart, "ViewProperty.txt", "ViewPropertySmart.txt"))
        Dim template As New CTemplate(fileName, TemplateFolderFragments)

        Dim s As String = String.Empty
        Dim i As Integer, j As Integer, dot As Integer, testColName As String, viewOnly As Boolean
        For i = 0 To dr_vw.FieldCount - 1
            viewOnly = True
            For j = 0 To dr_tbl.FieldCount - 1
                testColName = LCase(dr_vw.GetName(i))
                dot = testColName.IndexOf(".")
                If -1 <> dot Then testColName = testColName.Substring(dot + 1)
                If testColName = LCase(dr_tbl.GetName(j)) Then
                    viewOnly = False
                    Exit For
                End If
            Next
            If viewOnly Then s &= ColumnProperty(dr_vw, i, template, True)
        Next
        Return s
    End Function

    Private Function CompareToFunction() As String
        Return CompareToFunction(OrderByColumns)
    End Function
    Public Function CompareToFunction(ByVal orderByCols As String) As String
        Dim ss As String() = orderByCols.Split(CChar(","))
        Select Case ss.Length
            Case 1
                'Eg. "ExampleDate DESC"
                Dim column As String = RemoveDesc(OrderByColumns)
                Dim desc As Boolean = ContainsDesc(OrderByColumns)
                Return CompareToFunction1(TemplateFolderFragments, column, desc)

            Case 2
                'Eg. "ParentName, ExampleDate DESC"
                Dim column1 As String = RemoveDesc(ss(0))
                Dim column2 As String = RemoveDesc(ss(1))
                Dim desc1 As Boolean = ContainsDesc(ss(0))
                Dim desc2 As Boolean = ContainsDesc(ss(1))
                Return CompareToFunction2(TemplateFolderFragments, column1, desc1, column2, desc2)

            Case Else '(3)
                'Eg. "GrandParentName, ParentName, ExampleDate DESC"
                Dim column1 As String = RemoveDesc(ss(0))
                Dim column2 As String = RemoveDesc(ss(1))
                Dim column3 As String = RemoveDesc(ss(2))
                Dim desc1 As Boolean = ContainsDesc(ss(0))
                Dim desc2 As Boolean = ContainsDesc(ss(1))
                Dim desc3 As Boolean = ContainsDesc(ss(2))
                Return CompareToFunction3(TemplateFolderFragments, column1, desc1, column2, desc2, column3, desc3)
        End Select
    End Function
    Public Function CompareToFunction1(ByVal templateFolder As String, ByVal column As String, ByVal desc As Boolean) As String
        If String.IsNullOrEmpty(column) Then column = PrimaryKeyName
        With New CTemplate("CompareToSingle.txt", templateFolder)
            .Replace("ClassName", ClassName)
            .Replace("OrderByColumnsProperCase", ProperCase(column))
            .Replace("DESC", CStr(IIf(desc, "*-1", "")))
            Return .Template
        End With
    End Function
    Public Function CompareToFunction2(ByVal templateFolder As String, ByVal column1 As String, ByVal desc1 As Boolean, ByVal column2 As String, ByVal desc2 As Boolean) As String
        With New CTemplate("CompareToDouble.txt", templateFolder)
            .Replace("ClassName", ClassName)
            .Replace("FirstColumn", ProperCase(column1))
            .Replace("SecondColumn", ProperCase(column2))
            .Replace("DESC1", CStr(IIf(desc1, "*-1", "")))
            .Replace("DESC2", CStr(IIf(desc2, "*-1", "")))
            Return .Template
        End With
    End Function
    Public Function CompareToFunction3(ByVal templateFolder As String, ByVal column1 As String, ByVal desc1 As Boolean, ByVal column2 As String, ByVal desc2 As Boolean, ByVal column3 As String, ByVal desc3 As Boolean) As String
        With New CTemplate("CompareToTriple.txt", templateFolder)
            .Replace("ClassName", ClassName)
            .Replace("FirstColumn", ProperCase(column1))
            .Replace("SecondColumn", ProperCase(column2))
            .Replace("ThirdColumn", ProperCase(column3))
            .Replace("DESC1", CStr(IIf(desc1, "*-1", "")))
            .Replace("DESC2", CStr(IIf(desc2, "*-1", "")))
            .Replace("DESC3", CStr(IIf(desc3, "*-1", "")))
            Return .Template
        End With
    End Function
    Private Function RemoveDesc(ByVal s As String) As String
        Dim i As Integer = s.ToLower.IndexOf(" desc")
        If i = -1 Then Return Trim(s)
        Return Trim(s.Substring(0, i))
    End Function
    Private Function ContainsDesc(ByVal s As String) As Boolean
        Return s.ToLower.Contains(" desc")
    End Function
    Private Function ColumnProperty(ByVal dr As IDataReader, ByVal i As Integer, ByVal template As CTemplate, ByVal ignoreKeys As Boolean) As String
        Dim columnName As String = dr.GetName(i)
        Dim columnType As String = GetTypeString(dr, i)

        If ignoreKeys Then
            If LCase(columnName) = LCase(PrimaryKeyName) Then Return String.Empty
            If LCase(columnName) = LCase(SecondaryKeyName) And (Me.IsManyToMany Or Me.Is3Way) Then Return String.Empty
            If LCase(columnName) = LCase(TertiaryKeyName) And Me.Is3Way Then Return String.Empty
        End If

        With template
            .Reset()
            .Replace("ColumnNameProperCase", ProperCase(columnName))
            .Replace("ColumnNameCamelCase", CamelCase(columnName))
            .Replace("ColumnName", columnName)
            .Replace("DataType", columnType)
            .Replace("DataTypeVb", DataTypeVb(dr.GetFieldType(i)))
            Return .Template
        End With
    End Function
    Private Function GetTypeString(ByVal dr As IDataReader, ByVal colName As String) As String
        Return GetTypeString(dr, dr.GetOrdinal(colName))
    End Function
    Private Function GetTypeString(ByVal dr As IDataReader, ByVal i As Integer, Optional ByVal includeDefault As Boolean = False) As String
        Dim type As System.Type = dr.GetFieldType(i)
        Return ShortDataType(type, Me.Language)
    End Function
    Private Function InitValue(ByVal dr As IDataReader, ByVal colName As String) As String
        If Len(colName) = 0 Then Return String.Empty

        Dim i As Integer = dr.GetOrdinal(colName)
        If -1 = i Then Return String.Empty

        Dim type As System.Type = dr.GetFieldType(i)
        If Me.Language = ELanguage.CSharp Then
            If type.Equals(GetType(String)) Then Return "string.Empty"
            If type.Equals(GetType(Integer)) Then Return "int.MinValue"
            If type.Equals(GetType(Int16)) Then Return "int.MinValue"
            If type.Equals(GetType(Int32)) Then Return "int.MinValue"
            If type.Equals(GetType(Int64)) Then Return "long.MinValue"
            If type.Equals(GetType(Long)) Then Return "long.MinValue"
            If type.Equals(GetType(Double)) Then Return "double.NaN"
            If type.Equals(GetType(Single)) Then Return "single.NaN"
            If type.Equals(GetType(Decimal)) Then Return "decimal.MinValue"
            If type.Equals(GetType(DateTime)) Then Return "DateTime.MinValue"
            If type.Equals(GetType(Byte)) Then Return "0" '"int.MinValue" 'tinyint
            If type.Equals(GetType(Byte())) Then Return "new byte[]{}"
            If type.Equals(GetType(SByte)) Then Return "int.MinValue" '"null" 'tinyint
            If type.Equals(GetType(SByte())) Then Return "null"
            If type.Equals(GetType(Guid)) Then Return "Guid.Empty"
            If type.Equals(GetType(Boolean)) Then Return "false"
            If type.Equals(GetType(MySql.Data.Types.MySqlDateTime)) Then Return "DateTime.MinValue"
            Throw New Exception("No default value for type " & type.ToString())
        Else
            If type.Equals(GetType(String)) Then Return "String.Empty"
            If type.Equals(GetType(Integer)) Then Return "Integer.MinValue"
            If type.Equals(GetType(Int16)) Then Return "Integer.MinValue"
            If type.Equals(GetType(Int32)) Then Return "Integer.MinValue"
            If type.Equals(GetType(Int64)) Then Return "Long.MinValue"
            If type.Equals(GetType(Long)) Then Return "Long.MinValue"
            If type.Equals(GetType(Double)) Then Return "Double.NaN"
            If type.Equals(GetType(Single)) Then Return "Single.NaN"
            If type.Equals(GetType(Decimal)) Then Return "Decimal.MinValue"
            If type.Equals(GetType(DateTime)) Then Return "DateTime.MinValue"
            If type.Equals(GetType(Byte)) Then Return "0" '"Integer.MinValue" 'tinyint
            If type.Equals(GetType(Byte())) Then Return "New Byte(){}"
            If type.Equals(GetType(SByte)) Then Return "Integer.MinValue" '"Nothing" 'tinyint
            If type.Equals(GetType(SByte())) Then Return "Nothing"
            If type.Equals(GetType(Guid)) Then Return "Guid.Empty"
            If type.Equals(GetType(Boolean)) Then Return "False"
            If type.Equals(GetType(MySql.Data.Types.MySqlDateTime)) Then Return "DateTime.MinValue"
            Throw New Exception("No default value for type " & type.ToString())
        End If
    End Function
    Public Shared Function ShortDataType(ByVal type As System.Type, ByVal language As ELanguage) As String
        If type Is GetType(MySql.Data.Types.MySqlDateTime) Then type = GetType(DateTime)

        Dim s As String = type.ToString
        s = Replace(s, "System.", "")
        If language = ELanguage.VbNet Then
            s = Replace(s, "[]", "()")
            s = Replace(s, "Int32", "Integer")
            s = Replace(s, "Int16", "Integer")
            s = Replace(s, "Int64", "Long")
            s = Replace(s, "SByte()", "Byte()")
            s = Replace(s, "SByte", "Integer") 'tinyint
            's = Replace(s, "Byte", "Integer") 'tinyint
        Else
            s = Replace(s, "Int32", "int")
            s = Replace(s, "Int16", "int")
            s = Replace(s, "Int64", "long")
            s = Replace(s, "SByte[]", "byte[]")
            s = Replace(s, "SByte", "int") 'tinyint
            's = Replace(s, "Byte", "int") 'tinyint
            s = Replace(s, "Boolean", "bool")
            If s <> "DateTime" AndAlso s <> "Guid" Then s = s.ToLower
        End If
        Return s
    End Function
    Private Function DataTypeVb(ByVal type As System.Type) As String
        Return ShortDataType(type, ELanguage.VbNet)
    End Function
#End Region

#Region "Protected - Select Commands"
    Protected Function SelectCommands(ByVal dr As IDataReader, ByVal type As EQueryType) As String
        'If Me.UseCaching AndAlso type <> EQueryType.Trans Then Return String.Empty

        Dim fileName As String = CStr(IIf(Me.Architecture = EArchitecture.StoredProcs, "StoredProcQuery.txt", "SelectQuery.txt"))
        Select Case type
            Case EQueryType.Paged : fileName = fileName.Replace(".txt", "Paging.txt")
            Case EQueryType.Count : fileName = fileName.Replace(".txt", "Count.txt")
            Case EQueryType.Trans : fileName = fileName.Replace(".txt", "Transactional.txt")
        End Select

        Dim template As New CTemplate(fileName, TemplateFolderFragments)
        Dim sb As New StringBuilder()
        For Each i As String In Me.OptionalFilters
            If i = PrimaryKeyName AndAlso Not (IsManyToMany Or Is3Way) Then Continue For
            sb.Append(SelectCommand(dr, i, template))
        Next

        If Me.UseCaching Then
            If type = EQueryType.Trans Then
                If Language = ELanguage.CSharp Then
                    sb.Replace("public ", "internal ")
                Else
                    sb.Replace("Public ", "Friend ")
                End If
            Else
                If Language = ELanguage.CSharp Then
                    sb.Replace("public ", "protected ")
                Else
                    sb.Replace("Public ", "Protected ")
                End If
            End If
        End If

        Return sb.ToString
    End Function
    Protected Function SelectCommand(ByVal dr As IDataReader, ByVal name As String, ByVal template As CTemplate) As String
        Dim i As Integer = dr.GetOrdinal(name)
        Dim shortName As String = DropPrefix(dr, name)
        With template
            .Reset()
            .Replace("ClassName", ClassName)
            .Replace("ColumnName", name)
            .Replace("ColumnNameCamelCase", CamelCase(name))
            .Replace("ColumnNameProperCase", ProperCase(name))
            .Replace("ColumnNameShorter", ProperCase(shortName))
            .Replace("DataType", GetTypeString(dr, name))
            .Replace("Prefix", StoredProcNamePrefix)
            .Replace("TableName", Singular(TableName))
            Return .Template
        End With
    End Function
#End Region

#Region "Protected - ToXml"
    Protected Function ToXml(ByVal dr As IDataReader) As String
        With New CTemplate("ToXml.txt", TemplateFolderFragments)
            .Replace("ToXml_Items", ToXml_Items(dr))
            Return .Template
        End With
    End Function
    Protected Function ToXml_Items(ByVal dr As IDataReader) As String
        Dim sb As New StringBuilder()
        With New CTemplate("ToXml_Item.txt", TemplateFolderFragments)
            For i As Integer = 0 To dr.FieldCount - 1
                .Reset()
                .Replace("ColumnName", dr.GetName(i))
                .Replace("ProperCase", ProperCase(dr.GetName(i)))
                sb.Append(.Template)
            Next
        End With
        Return sb.ToString
    End Function
#End Region

#Region "Protected - AuditTrail"
    Protected Function AuditTrail(ByVal dr As IDataReader) As String
        Dim fileName As String = "AuditTrail.txt"
        If Me.IsManyToMany Then fileName = "AuditTrailM2M.txt"
        If Me.Is3Way Then fileName = "AuditTrail3Way.txt"

        With New CTemplate(fileName, TemplateFolderFragments)
            .Replace("ClassName", ClassName)
            .Replace("PrimaryKeyNameProperCase", ProperCase(PrimaryKeyName))
            .Replace("SecondaryKeyNameProperCase", ProperCase(SecondaryKeyName))
            .Replace("TertiaryKeyNameProperCase", ProperCase(TertiaryKeyName))
            .Replace("PrimaryKeyNameCamelCase", CamelCase(PrimaryKeyName))
            .Replace("SecondaryKeyNameCamelCase", CamelCase(SecondaryKeyName))
            .Replace("TertiaryKeyNameCamelCase", CamelCase(TertiaryKeyName))
            .Replace("PrimaryKeyType", PrimaryKeyObjectType(dr))
            .Replace("SecondaryKeyType", SecondaryKeyObjectType(dr))
            .Replace("TertiaryKeyType", TertiaryKeyObjectType(dr))
            Return .Template
        End With
    End Function
#End Region

#Region "Protected - Custom Indices"
    Protected Function CustomIndices(ByVal dr As IDataReader) As String
        'If Not Me.UseCaching Then Return String.Empty

        Dim template As New CTemplate("CustomIndex.txt", TemplateFolderFragments)
        Dim s As String = String.Empty
        Dim i As String
        For Each i In Me.OptionalFilters
            s &= CustomIndex(dr, i, template)
        Next
        Return s
    End Function
    Protected Function CustomIndex(ByVal dr As IDataReader, ByVal name As String, ByVal template As CTemplate) As String
        Dim i As Integer = dr.GetOrdinal(name)
        Dim shortName As String = DropPrefix(dr, name)
        With template
            .Reset()
            .Replace("ClassName", ClassName)
            .Replace("ForeignKey", name)
            .Replace("ForeignKeyProperCase", ProperCase(name))
            .Replace("ForeignKeyShort", ProperCase(shortName))
            .Replace("ForeignKeyCamelCase", CamelCase(shortName))
            .Replace("DataType", GetTypeString(dr, name))
            Return .Template
        End With
    End Function
    Protected Function DropPrefix(ByVal dr As IDataReader, ByVal colName As String) As String
        Dim names As New List(Of String)(dr.FieldCount)
        For i As Integer = 0 To dr.FieldCount - 1
            names.Add(dr.GetName(i))
        Next

        Return CTable.Shorter(colName, names)
    End Function
#End Region

#Region "GenericSorting"
    Public Function GenericSorting() As String
        If Me.Architecture <> EArchitecture.Smart Then Return String.Empty
        With New CTemplate("genericsorting.txt", TemplateFolderFragments)
            .Replace("ClassName", Me.ClassName)
            Return .Template
        End With
    End Function
#End Region

#Region "MoveUpDown"
    Public Function MoveUpDown(ByVal dr As IDataReader) As String
        If Me.Architecture = EArchitecture.StoredProcs Then Return String.Empty
        If String.IsNullOrEmpty(Me.SortingColumn) Then Return String.Empty
        With New CTemplate("moveupdown.txt", TemplateFolderFragments)
            .Replace("ClassName", Me.ClassName)
            .Replace("PrimaryKeyCamelCase", CamelCase(Me.PrimaryKeyName))
            .Replace("PrimaryKeyType", Me.PrimaryKeyObjectType(dr))
            .Replace("SortingColumn", Me.SortingColumn)
            Return .Template
        End With
    End Function
#End Region

#Region "CSV Export"
    Public Function CsvHeadings(ByVal dr As IDataReader) As String
        Dim sb As New StringBuilder()
        For i As Integer = 0 To dr.FieldCount - 1
            If sb.Length > 0 Then sb.Append(", ")
            sb.Append("""").Append(dr.GetName(i)).Append("""")
        Next
        Return sb.ToString()
    End Function
    Public Function CsvData(ByVal dr As IDataReader) As String
        Dim sb As New StringBuilder()
        For i As Integer = 0 To dr.FieldCount - 1
            If sb.Length > 0 Then sb.Append(", ")
            sb.Append("i.").Append(ProperCase(dr.GetName(i)))
        Next
        Return sb.ToString()
    End Function
#End Region

#Region "Data As Members"
    'Members
    Private Function ColumnMembers(ByVal dr As IDataReader) As String
        If Me.Architecture = EArchitecture.Smart Then Return String.Empty

        Dim template As New CTemplate("Member.txt", TemplateFolderFragments)
        Dim i As Integer
        Dim sb As New StringBuilder
        For i = 0 To dr.FieldCount - 1
            template.Reset()
            template.Replace("ColumnNameCamelCase", CamelCase(dr.GetName(i)))
            template.Replace("DataType", GetTypeString(dr, i, True))
            sb.Append(template.Template)
        Next

        With New CTemplate("Members.txt", TemplateFolderFragments)
            .Replace("Members", sb.ToString)
            Return .Template
        End With
    End Function

    'Copy Constructor
    Private Function CopyConstructor(ByVal dr As IDataReader) As String
        Dim template As New CTemplate("CopyConstructor.txt", TemplateFolderFragments)
        Dim i As Integer
        Dim sb As New StringBuilder
        For i = 0 To dr.FieldCount - 1
            template.Reset()
            template.Replace("ColumnNameCamelCase", CamelCase(dr.GetName(i)))
            template.Replace("ColumnNameProperCase", ProperCase(dr.GetName(i)))
            If Not (Me.IsAutoNumber AndAlso dr.GetName(i) = PrimaryKeyName) Then
                sb.Append(template.Template)
            End If
        Next
        Return sb.ToString
    End Function


    'ReadColumns + Initial values + Data
    Protected Function Persistance(ByVal dr_view As IDataReader, ByVal dr_table As IDataReader) As String
        If Me.Architecture = EArchitecture.Smart Then Return String.Empty

        With New CTemplate("Persistance.txt", TemplateFolderFragments)
            .Replace("ReadColumns", ReadColumns(dr_view))
            .Replace("Data", Data(dr_table))
            Return .Template
        End With
    End Function
    Protected Function ReadColumns(ByVal dr As IDataReader) As String
        Dim s As String = String.Empty
        With New CTemplate("ReadColumn.txt", TemplateFolderFragments)
            For i As Integer = 0 To dr.FieldCount - 1
                .Reset()
                .Replace("ColumnNameCamelCase", CamelCase(dr.GetName(i)))
                .Replace("FunctionName", FunctionName(dr.GetFieldType(i)))
                .Replace("ColumnName", dr.GetName(i))
                s &= .Template
            Next
        End With
        Return s
    End Function
    Private Function InitValues(ByVal dr As IDataReader) As String
        Dim s As String = String.Empty
        With New CTemplate("InitValue.txt", TemplateFolderFragments)
            For i As Integer = 0 To dr.FieldCount - 1
                Dim colName As String = dr.GetName(i)
                .Reset()
                .Replace("ColumnNameCamelCase", CamelCase(colName))
                .Replace("ColumnValue", InitValue(dr, colName))
                s &= .Template
            Next
        End With
        Return s
    End Function
    Private Function Data(ByVal dr As IDataReader) As String
        Dim s As String = String.Empty
        With New CTemplate("Data.txt", TemplateFolderFragments)
            For i As Integer = 0 To dr.FieldCount - 1
                Dim colName As String = dr.GetName(i)
                .Reset()
                .Replace("ColumnName", colName)
                .Replace("ColumnNameCamelCase", CamelCase(colName))
                s &= .Template
            Next
        End With
        Return s
    End Function
    Public Function FunctionName(ByVal type As System.Type) As String
        If type.Equals(GetType(String)) Then Return "GetStr"
        If type.Equals(GetType(Integer)) Then Return "GetInt"
        If type.Equals(GetType(Int16)) Then Return "GetInt"
        If type.Equals(GetType(Int32)) Then Return "GetInt"
        If type.Equals(GetType(Int64)) Then Return "GetLong"
        If type.Equals(GetType(Long)) Then Return "GetLong"
        If type.Equals(GetType(Byte)) Then Return "GetByte" 'tinyint
        If type.Equals(GetType(Decimal)) Then Return "GetDec"
        If type.Equals(GetType(Double)) Then Return "GetDbl"
        If type.Equals(GetType(Single)) Then Return "GetSingle"
        If type.Equals(GetType(Guid)) Then Return "GetGuid"
        If type.Equals(GetType(DateTime)) Then Return "GetDate"
        If type.Equals(GetType(Boolean)) Then Return "GetBool"
        If type.Equals(GetType(MySql.Data.Types.MySqlDateTime)) Then Return "GetDate"
        If type.Equals(GetType(Byte())) Then Return "GetBytes"
        If type.Equals(GetType(SByte)) Then Return "GetInt" '"GetSByte"
        If type.Equals(GetType(SByte())) Then Return "GetSBytes" 'Could be a guid or string?
        Throw New Exception("New Type: " & type.ToString())
    End Function
#End Region

#Region "Query Casting"
    Public Function QueryCasting(ByVal dr As IDataReader) As String
        Dim fileName As String = CStr(IIf(Me.Architecture = EArchitecture.StoredProcs, "QueryCastingStatic.txt", "QueryCastingDynamic.txt"))
        If UseCaching Then fileName = "QueryCastingProtected.txt"
        With New CTemplate(fileName, TemplateFolderFragments)
            If Is3Way Then
                .Append(New CTemplate("QueryCasting_3way.txt", TemplateFolderFragments).Template)
            ElseIf IsManyToMany Then
                .Append(New CTemplate("QueryCasting_m2m.txt", TemplateFolderFragments).Template)
            End If

            .Replace("ClassName", Me.ClassName)

            .Replace("PrimaryKeyNameCamelCase", CamelCase(PrimaryKeyName))
            .Replace("PrimaryKeyNameProperCase", ProperCase(PrimaryKeyName))
            .Replace("PrimaryKeyObjectType", PrimaryKeyObjectType(dr))

            .Replace("SecondaryKeyNameCamelCase", CamelCase(SecondaryKeyName))
            .Replace("SecondaryKeyNameProperCase", ProperCase(SecondaryKeyName))
            .Replace("SecondaryKeyObjectType", SecondaryKeyObjectType(dr))

            .Replace("TertiaryKeyNameCamelCase", CamelCase(TertiaryKeyName))
            .Replace("TertiaryKeyNameProperCase", ProperCase(TertiaryKeyName))
            .Replace("TertiaryKeyObjectType", TertiaryKeyObjectType(dr))

            .Replace("SelectCommandsSimple", SelectCommands(dr, EQueryType.Normal))
            .Replace("SelectCommandsPaging", SelectCommands(dr, EQueryType.Paged))
            .Replace("SelectCommandsCounting", SelectCommands(dr, EQueryType.Count))
            .Replace("SelectCommandsTransactional", SelectCommands(dr, EQueryType.Trans))

            Dim pks As New List(Of String)(3)
            pks.Add(PrimaryKeyName)
            If IsManyToMany Then pks.Add(SecondaryKeyName)
            If Is3Way Then pks.Add(TertiaryKeyName)
            .Replace("PrimaryKeyParameters", PK_ParamsUntyped(dr, pks))
            .Replace("PrimaryKeyParametersTyped", PK_ParamsTyped(dr, pks))

            Return .Template
        End With
    End Function
#End Region

#Region "Schema Info"
    Public Function SchemaInfo() As String
        If Me.Architecture = EArchitecture.StoredProcs Then Return SchemaInfoSp()
        With New CTemplate("SchemaInfo.txt", TemplateFolderFragments)
            .Replace("TableName", TableName)
            .Replace("ViewName", ViewName)
            .Replace("OrderByColumns", OrderByColumns)
            .Replace("SortingColumn", SortingColumn)
            Return .Template
        End With
    End Function
    Public Function SchemaInfoSp() As String
        With New CTemplate("SchemaInfoSp.txt", TemplateFolderFragments)
            .Replace("TableName", Singular(TableName))
            .Replace("Prefix", StoredProcNamePrefix)
            Return .Template
        End With
    End Function
#End Region

#Region "Caching"
    Public Function Caching() As String
        If Not UseCaching Then Return String.Empty
        With New CTemplate("CacheLogic.txt", TemplateFolderFragments)
            .Replace("ClassName", Me.ClassName)
            Return .Template
        End With
    End Function
#End Region

#Region "PrimaryKeyColumns"
    Public Function PrimaryKeyColumns(ByVal dr As IDataReader) As String
        Dim fileName As String
        If Me.PrimaryKeyType = EPrimaryKeyType.AutoNumber Then
            fileName = CStr(IIf(Me.Architecture <> EArchitecture.Smart, "ViewProperty.txt", "ViewPropertySmart.txt"))
        ElseIf Not Me.IsManyToMany Then
            fileName = CStr(IIf(Me.Architecture <> EArchitecture.Smart, "TablePropertySettablePK.txt", "TablePropertySmart.txt"))
        Else
            fileName = CStr(IIf(Me.Architecture <> EArchitecture.Smart, "TableProperty.txt", "TablePropertySmart.txt"))
        End If

        Dim template As New CTemplate(fileName, TemplateFolderFragments)

        PrimaryKeyColumns = ColumnProperty(dr, dr.GetOrdinal(PrimaryKeyName), template, False)

        If Me.Is3Way Or Me.IsManyToMany Then
            PrimaryKeyColumns &= ColumnProperty(dr, dr.GetOrdinal(SecondaryKeyName), template, False)
            If Me.Is3Way Then
                PrimaryKeyColumns &= ColumnProperty(dr, dr.GetOrdinal(TertiaryKeyName), template, False)
            End If
        End If
    End Function
    Public Function PrimaryKeyInfo(ByVal dr As IDataReader) As String
        Dim fileName As String = "PrimaryKeyInfo1.txt"
        If Me.IsManyToMany Then fileName = "PrimaryKeyInfo2.txt"
        If Me.Is3Way Then fileName = "PrimaryKeyInfo3.txt"

        With New CTemplate(fileName, TemplateFolderFragments)
            .Replace("PrimaryKeyName", PrimaryKeyName)
            .Append(PrimaryKeyProperty(dr, PrimaryKeyName))
            If Me.Language = ELanguage.VbNet Then
                .Replace("InsertPrimaryKey", CStr(IIf(Me.PrimaryKeyType = EPrimaryKeyType.AutoNumber, "False", "True")))
            Else
                .Replace("InsertPrimaryKey", CStr(IIf(Me.PrimaryKeyType = EPrimaryKeyType.AutoNumber, "false", "true")))
            End If

            If Me.Is3Way Or Me.IsManyToMany Then
                .Replace("SecondaryKeyName", SecondaryKeyName)
                .Append(PrimaryKeyProperty(dr, SecondaryKeyName))
                If Me.Is3Way Then
                    .Replace("TertiaryKeyName", TertiaryKeyName)
                    .Append(PrimaryKeyProperty(dr, TertiaryKeyName))
                End If
            End If
            Return .Template
        End With
    End Function
    Public Function PrimaryKeyProperty(ByVal dr As IDataReader, ByVal columnName As String) As String
        Dim fileName As String = CStr(IIf(Me.Architecture = EArchitecture.Smart, "KeyPropertySmart.txt", "KeyProperty.txt"))
        With New CTemplate(fileName, TemplateFolderFragments)
            If columnName = PrimaryKeyName Then
                .Replace("Level", "Primary")
            ElseIf columnName = SecondaryKeyName Then
                .Replace("Level", "Secondary")
            Else
                .Replace("Level", "Tertiary")
            End If
            .Replace("ColumnNameCamelCase", CamelCase(columnName))
            .Replace("ColumnName", columnName)
            .Replace("DataType", GetTypeString(dr, columnName))
            .Replace("DataTypeVb", DataTypeVb(dr.GetFieldType(dr.GetOrdinal(columnName))))
            Return .Template
        End With
    End Function
    Private Function PK_ParamsUntyped(ByVal dr As IDataReader, ByVal list As List(Of String)) As String
        Dim sb As New StringBuilder()
        For Each i As String In list
            If sb.Length > 0 Then sb.Append(", ")
            sb.Append(CamelCase(i))
        Next
        Return sb.ToString
    End Function
    Private Function PK_ParamsTyped(ByVal dr As IDataReader, ByVal list As List(Of String)) As String
        Dim sb As New StringBuilder()
        With New CTemplate("Searching_Param.txt", TemplateFolderFragments)
            For Each i As String In list
                .Reset()
                .Replace("Name", CamelCase(i))
                .Replace("DataType", GetTypeString(dr, i))

                If sb.Length > 0 Then sb.Append(", ")
                sb.Append(.Template)
            Next
        End With
        Return sb.ToString
    End Function
#End Region

#Region "Stored Procs"
    'Names
    Public ReadOnly Property SpNameInsert() As String
        Get
            Return StoredProcNamePrefix & Singular(TableName) & "Insert"
        End Get
    End Property
    Public ReadOnly Property SpNameUpdate() As String
        Get
            Return StoredProcNamePrefix & Singular(TableName) & "Update"
        End Get
    End Property
    Public ReadOnly Property SpNameDeleteById() As String
        Get
            Return StoredProcNamePrefix & Singular(TableName) & "DeleteById"
        End Get
    End Property
    Public ReadOnly Property SpNameSelectById() As String
        Get
            Return StoredProcNamePrefix & Singular(TableName) & "SelectById"
        End Get
    End Property
    Public ReadOnly Property SpNameSelectAll() As String
        Get
            Return StoredProcNamePrefix & Singular(TableName) & "SelectAll"
        End Get
    End Property
    Public ReadOnly Property SpNameSelectByColumnName(ByVal name As String) As String
        Get
            Return StoredProcNamePrefix & Singular(TableName) & "SelectBy" & name
        End Get
    End Property

    'Templates
    Public Function SpSelectAll(ByVal dr As IDataReader) As String
        Dim viewOrTableName As String = ViewName
        If Len(Trim(ViewName)) = 0 Then viewOrTableName = TableName

        Dim fileName As String = "SelectAll.txt"
        With New CTemplate(fileName, TemplateFolderStoredProcs)
            .Replace("SpNameSelectAll", SpNameSelectAll)
            .Replace("ColumnNames", SelectColumnNames(dr))
            .Replace("ViewOrTableName", viewOrTableName)
            .Replace("OrderBy", OrderByClause)
            Return .Template
        End With
    End Function
    Public Function SpSelectByColumnName(ByVal name As String, ByVal dr As IDataReader) As String
        Dim viewOrTableName As String = ViewName
        If Len(Trim(ViewName)) = 0 Then viewOrTableName = TableName

        Dim fileName As String = "SelectByColumnName.txt"
        With New CTemplate(fileName, TemplateFolderStoredProcs)
            .Replace("SpName", SpNameSelectByColumnName(name))
            .Replace("ColumnNames", SelectColumnNames(dr))
            .Replace("ViewOrTableName", viewOrTableName)
            .Replace("OrderBy", OrderByClause)
            .Replace("ColumnName", name)
            .Replace("SqlDataType", DataType(dr, dr.GetOrdinal(name)))
            Return .Template
        End With
    End Function
    Public Function SpSelectById(ByVal dr As IDataReader) As String
        Dim viewOrTableName As String = ViewName
        If Len(Trim(ViewName)) = 0 Then viewOrTableName = TableName

        Dim fileName As String = CStr(IIf(IsManyToMany, "SelectByIdM2M.txt", "SelectById.txt"))
        With New CTemplate(fileName, TemplateFolderStoredProcs)
            .Replace("SpNameSelectById", SpNameSelectById)
            .Replace("PrimaryKeyName", PrimaryKeyName)
            .Replace("PrimaryKeySqlDataType", DataType(dr, dr.GetOrdinal(PrimaryKeyName)))
            If HasColumn(dr, SecondaryKeyName) Then
                .Replace("SecondaryKeyName", SecondaryKeyName)
                .Replace("SecondaryKeySqlDataType", DataType(dr, dr.GetOrdinal(SecondaryKeyName)))
            End If
            .Replace("ColumnNames", SelectColumnNames(dr))
            .Replace("ViewOrTableName", viewOrTableName)
            .Replace("OrderBy", OrderByClause)
            Return .Template
        End With
    End Function
    Public Function SpDeleteById(ByVal dr As IDataReader) As String
        Dim fileName As String = CStr(IIf(IsManyToMany, "DeleteByIdM2M.txt", "DeleteById.txt"))
        With New CTemplate(fileName, TemplateFolderStoredProcs)
            .Replace("SpNameDeleteById", SpNameDeleteById)
            .Replace("PrimaryKeyName", PrimaryKeyName)
            .Replace("PrimaryKeySqlDataType", DataType(dr, dr.GetOrdinal(PrimaryKeyName)))
            If HasColumn(dr, SecondaryKeyName) Then
                .Replace("SecondaryKeyName", SecondaryKeyName)
                .Replace("SecondaryKeySqlDataType", DataType(dr, dr.GetOrdinal(SecondaryKeyName)))
            End If
            .Replace("TableName", TableName)
            Return .Template
        End With
    End Function
    Public Function SpInsert() As String
        Dim fileName As String = CStr(IIf(IsManyToMany, "Insert.txt", "Insert.txt"))
        With New CTemplate(fileName, TemplateFolderStoredProcs)
            .Replace("SpNameInsert", SpNameInsert)
            .Replace("SpNameSelectById", SpNameSelectById)
            .Replace("PrimaryKeyName", PrimaryKeyName)
            .Replace("SecondaryKeyName", SecondaryKeyName)
            .Replace("TableName", TableName)
            .Replace("Identity", CStr(IIf(IsAutoNumber, "@IDENTITY", PrimaryKeyName)))

            Dim dr As IDataReader = GetTable()
            Try
                .Replace("Parameters", Parameters(dr, True))
                .Replace("InsertColumnNames", InsertColumnNames(dr))
                .Replace("InsertColumnValues", InsertColumnValues(dr))
                dr.Close()
            Catch ex As Exception
                dr.Close()
                Throw ex
            End Try

            Return .Template
        End With
    End Function
    Public Function SpUpdate() As String
        Dim fileName As String = CStr(IIf(IsManyToMany, "UpdateM2M.txt", "Update.txt"))
        With New CTemplate(fileName, TemplateFolderStoredProcs)
            .Replace("SpNameUpdate", SpNameUpdate)
            .Replace("SpNameSelectById", SpNameSelectById)
            .Replace("PrimaryKeyName", PrimaryKeyName)
            .Replace("SecondaryKeyName", SecondaryKeyName)
            .Replace("TableName", TableName)
            .Replace("Identity", CStr(IIf(IsAutoNumber, "@IDENTITY", PrimaryKeyName)))

            Dim dr As IDataReader = GetTable()
            Try
                .Replace("Parameters", Parameters(dr, False))
                .Replace("UpdateNameValuePairs", UpdateNameValuePairs(dr))
                dr.Close()
            Catch ex As Exception
                dr.Close()
                Throw ex
            End Try

            Return .Template
        End With
    End Function
    Private Function SelectColumnNames(ByVal dr As IDataReader) As String
        Dim template As New CTemplate("ColumnName.txt", TemplateFolderStoredProcFrags)
        Dim i As Integer
        Dim s As String = String.Empty
        For i = 0 To dr.FieldCount - 1
            If Len(s) > 0 Then s &= "," & vbCrLf
            template.Reset()
            template.Replace("ColumnName", dr.GetName(i))
            s &= template.Template
        Next
        Return s
    End Function
    Private Function Parameters(ByVal dr As IDataReader, ByVal isInsert As Boolean) As String
        Dim template As New CTemplate("parameter.txt", TemplateFolderStoredProcFrags)
        Dim s As String = String.Empty
        Dim i As Integer
        For i = 0 To dr.FieldCount - 1
            Dim name As String = dr.GetName(i)
            If isInsert And Not IsManyToMany And IsAutoNumber And name = PrimaryKeyName Then Continue For
            With template
                If Len(s) > 0 Then s &= "," & vbCrLf
                s &= Parameter(dr, i, template)
            End With
        Next
        Return s
    End Function
    Private Function Parameter(ByVal dr As IDataReader, ByVal i As Integer, Optional ByVal template As CTemplate = Nothing) As String
        If IsNothing(template) Then template = New CTemplate("parameter.txt", TemplateFolderStoredProcFrags)
        With template
            .Reset()
            .Replace("ColumnName", dr.GetName(i))
            .Replace("DataType", DataType(dr, i))
            Return .Template
        End With
    End Function
    Protected Shared Function DataType(ByVal dr As IDataReader, ByVal i As Integer) As String
        Dim s As String = dr.GetDataTypeName(i)
        s = s.Replace("varchar", "varchar(255)")
        s = s.Replace("VARCHAR", "VARCHAR(255)")
        Return s
    End Function
    Private Function InsertColumnNames(ByVal dr As IDataReader) As String
        Dim s As String = String.Empty
        Dim i As Integer
        Dim startAt As Integer = CInt(IIf(IsManyToMany Or Not IsAutoNumber, 0, 1))
        For i = startAt To dr.FieldCount - 1
            If Len(s) > 0 Then s &= "," & vbCrLf & vbTab & vbTab
            s &= dr.GetName(i)
        Next
        Return s
    End Function
    Private Function InsertColumnValues(ByVal dr As IDataReader) As String
        Dim at As String = CStr(IIf(Database.IsMySql, "_", "@"))
        Dim s As String = String.Empty
        Dim i As Integer
        Dim startAt As Integer = CInt(IIf(IsManyToMany Or Not IsAutoNumber, 0, 1))
        For i = startAt To dr.FieldCount - 1
            If Len(s) > 0 Then s &= "," & vbCrLf & vbTab & vbTab
            s &= at & dr.GetName(i)
        Next
        Return s
    End Function
    Private Function UpdateNameValuePairs(ByVal dr As IDataReader) As String
        Dim at As String = CStr(IIf(Database.IsMySql, "_", "@"))
        Dim s As String = String.Empty
        Dim i As Integer
        Dim startAt As Integer = CInt(IIf(IsManyToMany, 2, 1))
        For i = startAt To dr.FieldCount - 1
            If Len(s) > 0 Then s &= "," & vbCrLf & vbTab & vbTab
            s &= dr.GetName(i) & vbTab & "= " & at & dr.GetName(i)
        Next
        If Len(s) = 0 Then Return at & dr.GetName(0) & "=" & at & dr.GetName(0)
        Return s
    End Function
    Private Function HasColumn(ByVal dr As IDataReader, ByVal colName As String) As Boolean
        Dim i As Integer
        For i = 0 To dr.FieldCount - 1
            If LCase(dr.GetName(i)) = LCase(colName) Then Return True
        Next
        Return False
    End Function
    Private Function OrderByClause() As String
        Dim s As String = Me.OrderByColumns
        's = s.Replace(", ", ",").Replace(",", "], [")
        If Len(s) > 0 Then s = "ORDER BY " & vbCrLf & vbTab & vbTab & s '"[" & s & "]"
        Return s
    End Function
#End Region

#Region "SaveAllDeleteAll"
    Public Function SaveAllDeleteAll(ByVal dr As IDataReader) As String
        With New CTemplate("SaveAllDeleteAll.txt", TemplateFolderFragments)
            .Replace("ClassName", ClassName)
            .Replace("PrimaryKeyName", PrimaryKeyName)
            .Replace("PrimaryKeyType", GetTypeString(dr, PrimaryKeyName))
            Return .Template
        End With
    End Function
#End Region

#Region "Search"
    Public Function Search(ByVal dr As IDataReader) As String
        'Get list of filter columns
        Dim list As New List(Of String)(OptionalFilters)
        For Each i As String In TableColumnNames
            If i.ToLower.Contains("name") AndAlso Not list.Contains(i) Then list.Add(i)
        Next

        Dim fileName As String = CStr(IIf(UseCaching, "Searching_Cached.txt", "Searching_NotCached.txt"))
        With New CTemplate(fileName, TemplateFolderFragments)
            .Replace("ClassName", ClassName)
            .Replace("PrimaryKeyName", PrimaryKeyName)
            .Replace("SearchParamsTyped", Search_ParamsTyped(dr, list))
            .Replace("SearchParamsUntyped", Search_ParamsUntyped(dr, list))
            If Not UseCaching Then
                .Replace("FiltersString", Search_SqlFilters(dr, list, True))
                .Replace("FiltersOther", Search_SqlFilters(dr, list, False))
            End If
            Return .Template
        End With
    End Function
    Public Function SearchList(ByVal dr As IDataReader) As String
        'Get list of non-indexed columns
        Dim list As New List(Of String)(OptionalFilters)
        For Each i As String In TableColumnNames
            If dr.GetFieldType(dr.GetOrdinal(i)) Is GetType(String) Then list.Add(i)
        Next

        With New CTemplate("Searching_Cached_List.txt", TemplateFolderFragments)
            .Replace("ClassName", ClassName)
            .Replace("Indexed", Search_IndexedFilters(dr, New List(Of String)(OptionalFilters)))
            .Replace("Filters", Search_MatchFilters(dr, list))
            .Replace("SearchParamsTyped", Search_ParamsTyped(dr, list))
            Return .Template
        End With
    End Function
    Private Function Search_ParamsTyped(ByVal dr As IDataReader, ByVal list As List(Of String)) As String
        Dim sb As New StringBuilder()
        With New CTemplate("Searching_Param.txt", TemplateFolderFragments)
            For Each i As String In list
                Dim name As String = ShortName(i)
                Dim type As String = GetTypeString(dr, i)
                If dr.GetFieldType(dr.GetOrdinal(i)) Is GetType(Boolean) Then type &= "?"
                If dr.GetFieldType(dr.GetOrdinal(i)) Is GetType(String) Then Continue For

                .Reset()
                .Replace("Name", CamelCase(name))
                .Replace("DataType", type)

                If sb.Length > 0 Then sb.Append(", ")
                sb.Append(.Template)
            Next
        End With
        Return sb.ToString
    End Function
    Private Function Search_ParamsUntyped(ByVal dr As IDataReader, ByVal list As List(Of String)) As String
        Dim sb As New StringBuilder()
        For Each i As String In list
            Dim name As String = ShortName(i)
            If dr.GetFieldType(dr.GetOrdinal(i)) Is GetType(String) Then Continue For
            sb.Append(", ")
            sb.Append(CamelCase(name))
        Next
        Return sb.ToString
    End Function
    Private Function Search_MatchFiltersSql(ByVal dr As IDataReader, ByVal list As List(Of String)) As String
        Dim t As New CTemplate("Searching_SqlFilterString.txt", TemplateFolderFragments)

        Dim sb As New StringBuilder()
        For Each i As String In list
            With t
                .Reset()
                .Replace("Name", i)
                sb.Append(.Template)
            End With
        Next
        Return sb.ToString
    End Function
    Private Function Search_SqlFilters(ByVal dr As IDataReader, ByVal list As List(Of String), ByVal stringColumns As Boolean) As String
        'Templates
        Dim int As New CTemplate("Searching_SqlFilterInt.txt", TemplateFolderFragments)
        Dim guid As New CTemplate("Searching_SqlFilterGuid.txt", TemplateFolderFragments)
        Dim bool As New CTemplate("Searching_SqlFilterBool.txt", TemplateFolderFragments)
        Dim str As New CTemplate("Searching_SqlFilterString.txt", TemplateFolderFragments)

        Dim sb As New StringBuilder()
        For Each i As String In list

            Dim index As Integer = dr.GetOrdinal(i)
            Dim type As Type = dr.GetFieldType(dr.GetOrdinal(i))
            Dim t As CTemplate = int

            If type Is GetType(String) <> stringColumns Then Continue For

            If type Is GetType(String) Then t = str
            If type Is GetType(Boolean) Then t = bool
            If type Is GetType(Guid) Then t = guid
            With t
                .Reset()
                .Replace("Name", i)
                .Replace("ShortName", CamelCase(ShortName(i)))
                .Replace("DataType", GetTypeString(dr, i))
                sb.Append(.Template)
            End With
        Next
        Return sb.ToString
    End Function
    Private Function Search_MatchFilters(ByVal dr As IDataReader, ByVal list As List(Of String)) As String
        'Only show string ones in match function, as int/bool probably have indexes
        Dim t As New CTemplate("Searching_MemoryFilterCustom_String.txt", TemplateFolderFragments)
        Dim sb As New StringBuilder()
        For Each i As String In list
            If Not dr.GetFieldType(dr.GetOrdinal(i)) Is GetType(String) Then Continue For
            If i.ToLower.EndsWith("id") Or i.ToLower.EndsWith("fk") Then Continue For 'e.g statusId might be a string
            t.Reset()
            t.Replace("Name", i)
            t.Replace("ShortName", ShortName(i))
            sb.Append(t.Template)
        Next
        Return sb.ToString
    End Function
    Private Function Search_IndexedFilters(ByVal dr As IDataReader, ByVal list As List(Of String)) As String
        'Only show string ones in match function, as int/bool probably have indexes
        Dim bool As New CTemplate("Searching_MemoryFilterIndexed_Bool.txt", TemplateFolderFragments)
        Dim int As New CTemplate("Searching_MemoryFilterIndexed_Int.txt", TemplateFolderFragments)
        Dim guid As New CTemplate("Searching_MemoryFilterIndexed_Guid.txt", TemplateFolderFragments)
        Dim str As New CTemplate("Searching_MemoryFilterIndexed_String.txt", TemplateFolderFragments)

        'Sample one
        If list.Count = 0 Then
            int.Replace("Name", "ForeignKey")
            int.Replace("ShortName", ShortName("ForeignKey"))
            int.Replace("CamelCase", CamelCase(ShortName("foreignKey")))
            int.Replace("DataType", CStr(IIf(Me.Language = ELanguage.CSharp, "int", "Integer")))
            Return int.Template
        End If

        Dim sb As New StringBuilder()
        For Each i As String In list
            Dim type As Type = dr.GetFieldType(dr.GetOrdinal(i))

            Dim t As CTemplate = int 'Standard template - normal fk
            If type Is GetType(Boolean) Then t = bool
            If type Is GetType(Guid) Then t = guid
            If type Is GetType(String) Then t = str

            'String matching handled in match function (so ignore here)
            If type Is GetType(String) AndAlso Not i.ToLower.EndsWith("id") AndAlso Not i.ToLower.EndsWith("fk") Then Continue For

            t.Reset()
            t.Replace("Name", i)
            t.Replace("ShortName", ShortName(i))
            t.Replace("CamelCase", CamelCase(ShortName(i)))
            t.Replace("DataType", GetTypeString(dr, i))
            sb.Append(t.Template)
        Next
        Return sb.ToString
    End Function
    Private Function PrimaryKeyConstantsInteger(ByVal pkType As Type) As String
        If Not pkType Is GetType(Integer) AndAlso Not pkType Is GetType(Byte) Then Return String.Empty
        With New CTemplate("PrimaryKeyConstants_Integer.txt", TemplateFolderFragments)
            .Replace("Singular", Singular)
            Return .Template
        End With
    End Function
    Private Function PrimaryKeyConstantsString(ByVal pkType As Type) As String
        If Not pkType Is GetType(String) Then Return String.Empty
        With New CTemplate("PrimaryKeyConstants_String.txt", TemplateFolderFragments)
            If UseCaching Then
                Return .Template
            Else
                Return String.Concat(vbCrLf, .Template)
            End If
        End With
    End Function
#End Region

End Class